Part A: Relational Database Management System (RDBMS) for Foodservice Australia

Q1. Creating the MySQL Database

a. SQL Statements to Create Tables:

CREATE TABLE Chef (
    chefID INT PRIMARY KEY AUTO_INCREMENT,
    chefFirstName VARCHAR(50),
    chefLastName VARCHAR(50),
    chefPhoneNumber VARCHAR(20),
    chefStreet VARCHAR(100),
    chefSuburb VARCHAR(50),
    chefState VARCHAR(50),
    chefPostCode VARCHAR(10),
    chefEmail VARCHAR(100)
);

CREATE TABLE FoodStall (
    foodStallID INT PRIMARY KEY AUTO_INCREMENT,
    foodStallName VARCHAR(100),
    foodStallLocation VARCHAR(100)
);

CREATE TABLE Customer (
    customerID INT PRIMARY KEY AUTO_INCREMENT,
    customerFirstName VARCHAR(50),
    customerLastName VARCHAR(50),
    customerPhoneNumber VARCHAR(20),
    customerStreet VARCHAR(100),
    customerSuburb VARCHAR(50),
    customerState VARCHAR(50),
    customerPostCode VARCHAR(10),
    customerEmail VARCHAR(100)
);

CREATE TABLE FoodStallKeeper (
    foodStallKeeperID INT PRIMARY KEY AUTO_INCREMENT,
    foodStallKeeperFirstName VARCHAR(50),
    foodStallKeeperLastName VARCHAR(50),
    foodStallKeeperPhoneNumber VARCHAR(20),
    foodStallKeeperStreet VARCHAR(100),
    foodStallKeeperSuburb VARCHAR(50),
    foodStallKeeperState VARCHAR(50),
    foodStallKeeperPostCode VARCHAR(10),
    foodStallKeeperEmail VARCHAR(100),
    foodStallID INT,
    FOREIGN KEY (foodStallID) REFERENCES FoodStall(foodStallID)
);

CREATE TABLE Dish (
    dishID INT PRIMARY KEY AUTO_INCREMENT,
    dishTitle VARCHAR(100),
    dishCuisine VARCHAR(50),
    dishDescription TEXT,
    dishIngredients TEXT,
    dishUnitPrice DECIMAL(10, 2),
    chefID INT,
    foodStallID INT,
    FOREIGN KEY (chefID) REFERENCES Chef(chefID),
    FOREIGN KEY (foodStallID) REFERENCES FoodStall(foodStallID)
);

CREATE TABLE DineInOrder (
    dineInOrderID INT PRIMARY KEY AUTO_INCREMENT,
    dishID INT,
    customerID INT,
    quantity INT,
    orderDate DATE,
    timeForPreparation TIME,
    serviceCharge DECIMAL(10, 2),
    FOREIGN KEY (dishID) REFERENCES Dish(dishID),
    FOREIGN KEY (customerID) REFERENCES Customer(customerID)
);

CREATE TABLE TakeAwayOrder (
    takeAwayOrderID INT PRIMARY KEY AUTO_INCREMENT,
    dishID INT,
    customerID INT,
    quantity INT,
    orderDate DATE,
    timeForCollection TIME,
    FOREIGN KEY (dishID) REFERENCES Dish(dishID),
    FOREIGN KEY (customerID) REFERENCES Customer(customerID)
);

======================================================================
Q2. SQL Queries and Results
a. List all Chefs in Clayton:
SELECT * FROM dbmbis403.Chef WHERE chefSuburb = 'Clayton';

b. List DineInOrder IDs for Italian Dishes:
SELECT dbmbis403.dineInOrderID FROM dbmbis403.DineInOrder
INNER dbmbis403.JOIN Dish ON DineInOrder.dishID = Dish.dishID
WHERE Dish.dishCuisine = 'Italian';

c. List Food Stall Keepers of Italian Food Stalls:
SELECT foodStallKeeperID, CONCAT(foodStallKeeperFirstName, ' ', foodStallKeeperLastName) AS foodStallKeeperFullName
FROM dbmbis403.FoodStallKeeper
INNER JOIN dbmbis403.FoodStall ON FoodStallKeeper.foodStallID = FoodStall.foodStallID
INNER JOIN dbmbis403.Dish ON FoodStall.foodStallID = Dish.foodStallID
WHERE Dish.dishCuisine = 'Italian';


d. List Customers with Dine-In Orders:
SELECT c.customerID, CONCAT(c.customerFirstName, ' ', c.customerLastName) AS customerFullName, 
COUNT(d.dineInOrderID) AS NumberOfDineInOrders FROM dbmbis403.Customer c
LEFT JOIN dbmbis403.DineInOrder d ON c.customerID = d.customerID GROUP BY 
    c.customerID, c.customerFirstName, c.customerLastName;


e. List Customers with Dine-In and Take-Away Orders:
SELECT Customer.customerID, CONCAT(Customer.customerFirstName, ' ', Customer.customerLastName) AS customerFullName, 
    COUNT(DISTINCT DineInOrder.dineInOrderID) AS NumberOfDineInOrders, 
    COUNT(DISTINCT TakeAwayOrder.takeAwayOrderID) AS NumberOfTakeAwayOrders
FROM Customer LEFT JOIN DineInOrder ON Customer.customerID = DineInOrder.customerID
LEFT JOIN TakeAwayOrder ON Customer.customerID = TakeAwayOrder.customerID
GROUP BY Customer.customerID;



f. List Chefs with Multiple Dishes:
SELECT Chef.chefID, Chef.chefFirstName, Chef.chefLastName, COUNT(Dish.dishID) AS NumberOfDishes 
FROM Chef INNER JOIN Dish ON Chef.chefID = Dish.chefID 
GROUP BY Chef.chefID, Chef.chefFirstName, Chef.chefLastName 
HAVING COUNT(Dish.dishID) > 1 ORDER BY NumberOfDishes DESC, Chef.chefFirstName ASC;


g. List Dishes Ordered Multiple Times for Take-Away:
SELECT Dish.dishID, Dish.dishTitle, COUNT(TakeAwayOrder.takeAwayOrderID) AS NumberOfOrders 
FROM Dish INNER JOIN TakeAwayOrder ON Dish.dishID = TakeAwayOrder.dishID 
WHERE TakeAwayOrder.orderDate BETWEEN '2024-12-02' AND '2024-12-04' GROUP BY 
    Dish.dishID, Dish.dishTitle HAVING COUNT(TakeAwayOrder.takeAwayOrderID) > 1;



h. List Dishes with No Orders:
SELECT Dish.dishID, Dish.dishTitle, COUNT(TakeAwayOrder.takeAwayOrderID) AS NumberOfOrders 
FROM Dish INNER JOIN TakeAwayOrder ON Dish.dishID = TakeAwayOrder.dishID 
WHERE TakeAwayOrder.orderDate BETWEEN '2024-12-02' AND '2024-12-04' GROUP BY 
    Dish.dishID, Dish.dishTitle HAVING COUNT(TakeAwayOrder.takeAwayOrderID) > 1;



i. List Food Stalls with Total Take-Away Order Sales:
SELECT FoodStall.foodStallID, FoodStall.foodStallName, SUM(Dish.dishUnitPrice * TakeAwayOrder.quantity) AS TotalTakeAwayOrderSales
FROM FoodStall
INNER JOIN Dish ON FoodStall.foodStallID = Dish.foodStallID
INNER JOIN TakeAwayOrder ON Dish.dishID = TakeAwayOrder.dishID
GROUP BY FoodStall.foodStallID, FoodStall.foodStallName;


